package presale_house.service.impl;

import presale_house.entity.ClientDo;
import presale_house.entity.HouseDo;
import presale_house.entity.HouseLayout;
import presale_house.req.ClientRequest;
import presale_house.req.HouseRequest;
import presale_house.res.TableDTO;
import presale_house.service.ClientService;
import presale_house.service.HouseService;
import presale_house.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 * StudentService的实现类,实现mysql语句完成对表的操作
 */
public class HouseServiceImpl implements HouseService {
    @Override
    public TableDTO retrieveHouse(HouseRequest request) {
        //retrieveHouses
        StringBuilder sql=new StringBuilder();
        sql.append("select * from house ");
        if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())){//判断是否为空
            sql.append(" where house_layout like '%"+request.getSearchKey().trim()+"%' ");
        }
        //分页查询
        sql.append("order by id asc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        TableDTO returnDTO=new TableDTO();
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            rs= ps.executeQuery();
            //查询记录
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from house ");
            if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())){
                sql.append(" where house_layout like '%"+request.getSearchKey().trim()+"%' ");
            }
            ps=conn.prepareStatement(sql.toString());
            rs= ps.executeQuery();
            while(rs.next()){
                int count=rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;

        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }


    /**
     * 添加房屋
     * @param houseDo
     * @return
     */
    @Override
    public boolean add(HouseDo houseDo) {
        StringBuilder sql=new StringBuilder();
        sql.append(" insert into house(house_layout,area,features,state) ");
        sql.append(" values(?,?,?,?) ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setString(1, houseDo.getHouse_layout());
            ps.setString(2, houseDo.getArea());
            ps.setString(3, houseDo.getFeatures());
            ps.setString(4, houseDo.getState());
            return ps.executeUpdate()==1;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    /**
     * 通过ID获取客户信息
     * @param selectedHouseId
     * @return
     */
    @Override
    public HouseDo getById(int selectedHouseId) {
        StringBuilder sql=new StringBuilder("select * from house where id=? ");
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        HouseDo houseDo =new HouseDo();
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setInt(1, selectedHouseId);
            rs=ps.executeQuery();

            while(rs.next()){
                //处理每一条查询
                int id= rs.getInt("id");
                String house_layout= rs.getString("house_layout");
                String area= rs.getString("area");
                String features= rs.getString("features");
                String state=rs.getString("state");
                houseDo.setId(id);
                houseDo.setHouse_layout(house_layout);
                houseDo.setArea(area);
                houseDo.setFeatures(features);
                houseDo.setState(state);
            }
            return houseDo;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }


    /**
     * 更新房屋信息
     * @param houseDo
     * @return
     */
    @Override
    public boolean update(HouseDo houseDo) {
        StringBuilder sql=new StringBuilder();
        sql.append(" update house set house_layout=?,area=?,features=?,state=? ");
        sql.append(" where id =? ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setString(1, houseDo.getHouse_layout());
            ps.setString(2, houseDo.getArea());
            ps.setString(3, houseDo.getFeatures());
            ps.setString(4, houseDo.getState());
            ps.setInt(5, houseDo.getId());
            return ps.executeUpdate()==1;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    /**
     * 删除房屋信息
     * @param selectedHouseIds
     * @return
     */
    @Override
    public boolean delete(int[] selectedHouseIds) {
        StringBuilder sql=new StringBuilder();
        sql.append(" delete from house where id in ( ");
        for (int i = 0; i < selectedHouseIds.length; i++) {
            if(i== selectedHouseIds.length-1){
                sql.append(" ? ");
            }else{
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());

            for (int i = 0; i < selectedHouseIds.length; i++) {
                //设置参数,从1开始
                ps.setInt(i+1, selectedHouseIds[i]);
            }
            return ps.executeUpdate()== selectedHouseIds.length;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    /**
     * 修改客户数据时,回调客户数据
     * @param rs
     * @return
     * @throws SQLException
     */
    private static Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>>data =new Vector<>();
        while(rs.next()){
            //处理每一条查询
            Vector<Object>oneRecord=new Vector<>();
            int id= rs.getInt("id");
            String house_layout= rs.getString("house_layout");
            String area= rs.getString("area");
            String features= rs.getString("features");
            String state= rs.getString("state");
            oneRecord.addElement(id);
            oneRecord.addElement(house_layout);
            oneRecord.addElement(area);
            oneRecord.addElement(features);
            oneRecord.addElement(state);
            data.addElement(oneRecord);
        }
        return data;
    }
}
